package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.BankAccount;
import ModelLayer.Landlord;


public class DBLandlord implements IFDBLandlord {

	private Connection  con;
	private static DBLandlord dbLand;
	
	private DBLandlord(){
		con = DBConnection.getInstance().getDBcon();
	}
	
	public static DBLandlord getInstance(){
		if(dbLand==null) dbLand = new DBLandlord();
		return dbLand;
	}
	
	public ArrayList<Landlord> getAll_where(String wClause) {
		return miscWhere(wClause);
	}
	
	
	public ArrayList<Landlord> searchRandom(String searchF) {
		
		return miscWhere("l.pid LIKE '"+searchF+"' OR fname LIKE '"+searchF+"%' OR lname LIKE '"+searchF+"%' OR address LIKE '"+searchF+"%' OR city LIKE '"+searchF+"%' OR postalCode LIKE '"+searchF+"%' OR country LIKE '"+searchF+"%' OR phone LIKE '"+searchF+"%' OR email LIKE '"+searchF+"%' OR accountno LIKE '"+searchF+"%'");
	}
	
	public Landlord getLandlordById(int landlordID, boolean goDeep){
		return singleWhere("l.pid="+landlordID,goDeep);
	}
	
	public Landlord getLandlordByName(String name){
		return singleWhere("fname LIKE '"+name+"%' OR lname LIKE '"+name+"%'",true);
	}
	
	public Landlord getLandlordByName(String fname,String lname){
		return singleWhere("(fname LIKE '"+fname+"%' AND lname LIKE '"+lname+"%') OR (lname LIKE '"+fname+"%' AND fname LIKE '"+lname+"%')",true);
	}
	
	public ArrayList<Landlord> getAll(){
		return miscWhere("");
	}
	
	public ArrayList<Landlord> getAllLandlordsByName(String name){
		return miscWhere("fname='"+name+"' OR lname='"+name+"'");
	}
	
	
	
	public int insert(Landlord landlord){
		int rc = -1;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
		
			
			// START Location 
			int locationId =  DBLocation.getInstance().insert(landlord.getPostalCode(), landlord.getCity(), landlord.getCountry());
			// END Location
			
			
			// START BankDetails
			rc = DBBankAccount.getInstance().insert(new BankAccount(landlord.getBankAccount().getAccountNo(), landlord.getBankAccount().getAccountName(), landlord.getBankAccount().getBankName(), landlord.getBankAccount().getBranchCode()));
			// END BankDetails
			
			if(rc==-1) return rc;
			
			// START People
			String query = "INSERT INTO People (fname,lname,phone,address,email,locationId) VALUES ('"+landlord.getFname()+"','"+landlord.getLname()+"','"+landlord.getPhone()+"','"+landlord.getAddress()+"','"+landlord.getEmail()+"',"+locationId+")";
			
			rc = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			ResultSet res = stmt.getGeneratedKeys();
			
			if (res.next())
			{
				rc = res.getInt(1);
				
				// START Landlord
				String query2 = "INSERT INTO Landlord (pid,bankAccountNo) VALUES ("+rc+",'"+landlord.getBankAccount().getAccountNo()+"')";
				rc = stmt.executeUpdate(query2);
				//END Landlord
			}
			// END People
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert Landlord: " +e.getMessage());
		}
		
		return rc;
	}
	
	
	
	
	
	public int update(Landlord landlord)
	{
		int rc = -1;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			// START Location 
			int locationId =  DBLocation.getInstance().insert(landlord.getPostalCode(), landlord.getCity(), landlord.getCountry());
			// END Location
			
			// START BankDetails
			rc = DBBankAccount.getInstance().insert(new BankAccount(landlord.getBankAccount().getAccountNo(), landlord.getBankAccount().getAccountName(), landlord.getBankAccount().getBankName(), landlord.getBankAccount().getBranchCode()));
			if(rc==-1) return rc;
			// END BankDetails
			
			// START People
			String query = "UPDATE People SET fname = '"+landlord.getFname()+"',lname ='"+landlord.getLname()+"',phone ='"+landlord.getPhone()+"',address ='"+landlord.getAddress()+"',email ='"+landlord.getEmail()+"',locationId="+locationId+" WHERE pid = "+landlord.getPid();
			
			PreparedStatement psstmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			rc = psstmt.executeUpdate();
			ResultSet res = psstmt.getGeneratedKeys();
			
			if (res.next())
			{
				rc = res.getInt(1);
				
				// START Landlord
				String query2 = "UPDATE Landlord SET bankAccountNo = '"+landlord.getBankAccount().getAccountNo()+"' WHERE pid = "+landlord.getPid()+"";	
				rc = stmt.executeUpdate(query2);
				//END Landlord
			}
			// END People
			stmt.close();
			
		}catch(Exception e){
			System.err.println("Could not update the Landlord: " + e.getMessage());
		}
		
		return rc;
	}
	
	public int delete(int landlordID){
		int rc = -1;
		String query1 = "DELETE FROM People WHERE pid="+landlordID;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			stmt.close();
		}catch(Exception e){
			System.err.println("Could not delete Landlord "+e.getMessage());
		}
		return rc;
	}
	
	private Landlord singleWhere(String wClause, boolean goDeep){
		Landlord landlord = new Landlord();
		
		ResultSet res;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			res = stmt.executeQuery(query);
			
			if(res.next()){
				landlord = buildLandlord(res,goDeep);
			}
			stmt.close();
		}catch(Exception e){
			System.err.println("Could not execute select Landlord query "+e.getMessage());
		}
		
		return landlord;
	}
	
	private ArrayList<Landlord> miscWhere(String wClause){
		ArrayList<Landlord> landlords = new ArrayList<>();
		
		ResultSet res;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			res = stmt.executeQuery(query);
			
			while(res.next()){
				landlords.add(buildLandlord(res,true));
			}
			
			stmt.close();
		}catch(Exception e){
			System.err.println("Could not execute select multiple Landlord query "+e.getMessage());
		}
		
		return landlords;
	}
	
	private String buildQuery(String wClause){
		String query = "SELECT l.pid,fname,lname,phone,address,email,regDate,city,postalCode,country,bankAccountNo FROM Landlord l INNER JOIN People p ON l.pid=p.pid INNER JOIN Location loc ON p.locationId=loc.locationId INNER JOIN BankAccount bacc ON l.bankAccountNo = bacc.accountNo";
		if(wClause.length()>0) query += " WHERE "+wClause;
		return query;
	}
	
	private Landlord buildLandlord(ResultSet res, boolean goDeep){
		Landlord landlord = new Landlord();
		try{
			landlord.setPid(res.getInt("pid"));
			landlord.setFname(res.getString("fname"));
			landlord.setLname(res.getString("lname"));
			landlord.setPhone(res.getString("phone"));
			landlord.setAddress(res.getString("address"));
			landlord.setEmail(res.getString("email"));
			landlord.setDate(res.getTimestamp("regDate"));
			landlord.setPostalCode(res.getString("postalCode"));
			landlord.setCity(res.getString("city"));
			landlord.setCountry(res.getString("country"));
			if(goDeep) landlord.setBankAccount(DBBankAccount.getInstance().getBankAccount(res.getString("bankAccountNo")));
		}catch(Exception e) {
			System.err.println("Could not build the landlord "+e.getMessage());
		}
		return landlord;
	}
}
